package net.ptcs.litang.dao;

import net.ptcs.litang.util.DBUtils_pool;
import net.ptcs.litang.util.ResultSetUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 票数据操作层
 *
 * @author 赵浩森
 * @version 1.0
 * @since 2024/6/16 23:44
 */
public class TicketDao {
    private static final String QUERY_ALL_TICKET = "SELECT lts.id    AS id,\n" +
            "       oltc.city AS ocity,\n" +
            "       dltc.city AS dcity,\n" +
            "       start_time,\n" +
            "       end_time\n" +
            "FROM litang_ticket_stock lts\n" +
            "         LEFT JOIN litang_tab_city oltc\n" +
            "                   ON lts.origin_city_id = oltc.id\n" +
            "         LEFT JOIN litang_tab_city dltc\n" +
            "                   ON lts.destination_city_id = dltc.id ";

    private static final String UPDATE_STOCK = "UPDATE litang_ticket_stock\n" +
            "SET quantity = quantity - 1\n" +
            "WHERE id = ?";
    private static final String CHECK_ORDER = "SELECT id FROM litang_ticket_order WHERE stock_id = ?";
    private static final String INSERT_ORDER = "INSERT INTO litang_ticket_order\n" +
            "    (stock_id, user_id, order_time)\n" +
            "VALUES (?, ?, NOW())";

    public JSONArray selectTicketAll() throws ClassNotFoundException, SQLException {
        JSONArray dataContent = null;

        Connection conn = null;
        PreparedStatement stmt=null;
        ResultSet resultSet =null;

        try {
            conn = DBUtils_pool.getConnection();
            stmt = conn.prepareStatement(QUERY_ALL_TICKET);
            dataContent = ResultSetUtil.resultSetToJson(stmt.executeQuery());
            return dataContent;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtils_pool.release(conn, stmt, resultSet);
        }
    }


    public String orderTicket(Integer id, Integer userId) throws ClassNotFoundException {
        Connection conn = null;
        PreparedStatement stmt=null;
        ResultSet resultSet =null;
        try {
            conn = DBUtils_pool.getConnection();

            stmt = conn.prepareStatement(UPDATE_STOCK);
            stmt.setInt(1, id);
            stmt.executeUpdate();

            stmt = conn.prepareStatement(CHECK_ORDER);
            stmt.setInt(1, id);
            resultSet = stmt.executeQuery();

            if (!resultSet.next()) {
                stmt = conn.prepareStatement(INSERT_ORDER);
                stmt.setInt(1, id);
                stmt.setInt(2, userId);
                stmt.executeUpdate();
                return "1";
            }
            return "已订购过";
        } catch (SQLException e) {
            e.printStackTrace();
            return "订购失败";
        }finally {
            DBUtils_pool.release(conn, stmt, resultSet);
        }
    }

}